﻿CREATE PROCEDURE [usix].[s_Export_Short_ALL]
AS
SET NOCOUNT ON;

EXEC sh_Debug_PRINT 'usix.s_Export_Short_ALL'

--EXEC sp__Context_ArchiveOff 1
/*
TRUNCATE TABLE t_Loan_Usix
SET IDENTITY_INSERT t_Loan_Usix ON
INSERT INTO t_Loan_Usix(LoanID,ApplID,UsixID,CompanyID_Raw)
SELECT    CI.LoanID, DM_EXP.[Open Field#01],DM_EXP.[Customer #], DM_EXP.CompanyID_Raw
FROM         t_Loan_Borrower AS L_Bs INNER JOIN
t_CarInfo AS CI ON L_Bs.LoanID = CI.LoanID INNER JOIN
t_Borrower AS B ON L_Bs.BorrowerID = B.ID AND L_Bs.Pos = 0 INNER JOIN
AFCBANK.DataMining.dbo.t_All_Cust_Loan_Repo_PVT AS DM_EXP ON B.SSN = DM_EXP.[Primary SSN] AND 
(CI.VIN_L9 = DM_EXP.VIN_L9 OR CI.VIN_8R = DM_EXP.VIN_8R) OR
CI.LoanID = DM_EXP.[Open Field#01]
SET IDENTITY_INSERT t_Loan_Usix OFF
SELECT COUNT(*) FROM t_Loan_Usix
*/
DECLARE @UsixID varchar(40),@CompanyID int,@OF1 int
DECLARE curLU CURSOR READ_ONLY FOR
SELECT [Open Field#01],CF.[Customer #], CF.CompanyID_Raw FROM t_Loan_Usix L_U
RIGHT OUTER JOIN AFCBANK.DataMining.dbo.t_All_Cust_Loan_Repo_PVT CF
	ON L_U.UsixID = CF.[Customer #] AND L_U.CompanyID_Raw = CF.CompanyID_Raw
WHERE L_U.UsixID IS NULL-- AND [Customer #] = 'DA109213'

OPEN curLU FETCH NEXT FROM curLU INTO @OF1,@UsixID,@CompanyID
WHILE @@fetch_status = 0 BEGIN
	PRINT 'New: '+@UsixID+','+STR(@CompanyID,1)+' @ '+ CONVERT(varchar,GETDATE(),108)
	INSERT INTO t_Loan(StatusID,ApplicantID)VALUES(dbo.fs_StateID__Processing__FundedInUSIX(),@OF1)
	IF @@ERROR <> 0 RETURN 0
	INSERT INTO t_Loan_Usix(LoanID,ApplID,UsixID,CompanyID_Raw)
	SELECT SCOPE_IDENTITY(),@OF1,@UsixID,@CompanyID
	FETCH NEXT FROM curCU INTO @OF1,@UsixID,@CompanyID
END
CLOSE curLU DEALLOCATE curLU


/*
-----------------
SELECT     CF.[Open Field#01], CF.[Customer #], CF.CompanyID_Raw
FROM         t_Loan_USIX AS L_U RIGHT OUTER JOIN
                      AFCBANK.DataMining.dbo.t_All_Cust_Loan_Repo_PVT AS CF ON L_U.UsixID = CF.[Customer #] AND L_U.CompanyID_Raw = CF.CompanyID_Raw
WHERE     (L_U.UsixID IS NULL)
*/

DECLARE curEFM CURSOR
READ_ONLY FOR
SELECT [Customer #],CompanyID_Raw FROM usix.t_CustomerFile
ORDER BY [G_L Date] DESC

OPEN curEFM FETCH NEXT FROM curEFM INTO @UsixID,@CompanyID
WHILE @@fetch_status = 0 BEGIN
	PRINT 'Doing: '+@UsixID+','+STR(@CompanyID,1)+' @ '+ CONVERT(varchar,GETDATE(),108)
	BEGIN TRY
		EXEC usix.s_Export_Short @UsixID,@CompanyID
	END TRY BEGIN CATCH
--		    ERROR_NUMBER() AS ErrorNumber,
--        ERROR_SEVERITY() AS ErrorSeverity,
--        ERROR_STATE() AS ErrorState,
--        ERROR_PROCEDURE() AS ErrorProcedure,
--        ERROR_LINE() AS ErrorLine,
        PRINT ERROR_MESSAGE()
	END CATCH
	IF dbo.Stop() = 1 BREAK
	FETCH NEXT FROM curEFM INTO @UsixID,@CompanyID
END

CLOSE curEFM DEALLOCATE curEFM

